CREATE OR REPLACE PACKAGE BODY demo AS

procedure startup is
begin
        htp.htmlOpen;
        htp.headOpen;
        htp.title('DBPrism - IAS mod_plsql compatibility');
        htp.headClose;
        htp.bodyOpen(cattributes=>'aLink=#008040 bgColor=#ffffff link=#0000ff text=#000000 vLink=#ff0000');
        htp.center(htf.header(3,owa_util.get_cgi_env('SERVER_SOFTWARE')||' Demo Page'));
        htp.uListOpen;
        htp.listItem(htf.anchor('demo.test','Input Form demo')||
                ' Simple Form Demo in PLSQL, post action in XML, showing CGI vars, cookies');
        htp.listItem(htf.anchor('demo.test_textarea','Input form with textareas')||
                ' Simple Demo to test 32Kb limit of arguments of mod_plsql (DBPrism allows arguments of CLOB without size limits)');
        htp.listItem(htf.anchor('!demo.flexible?a=1&b=2&a=3','Flexible parameter passing')||
                ' Simple Output form in PLSQL which receives parameters in a flexible form, showing CGI vars, cookies');
        htp.listItem(htf.anchor('!demo.flexible','Flexible parameter passing without arguments')||
                ' Simple Output form in PLSQL which receives empty parameters in a flexible form');
        htp.listItem(htf.anchor('demo.show_form','Upload demo in PLSQL (IAS 1.0 compatibility)')||
                ' Simple Upload/Download Demo in IAS support');
        htp.br;
        htp.listItem(htf.anchor('m_tx.tx_start','Transactional demo in PLSQL and XML (shows new functionality of JTA jdbc drivers')||
                ' DBPrism transaction support by url demarcation');
        htp.br;
        htp.listItem(htf.anchor('demo.source_xtp','Source of XTP (XML toolkit procedures)')||
                ' Need owa_util.showsource package');
        htp.listItem(htf.anchor('demo.source_xtf','Source of XTF (XML toolkit functions)')||
                ' Need owa_util.showsource package');
        htp.listItem(htf.anchor('demo.source','Source of this Demo ')||
                ' Need owa_util.showsource package');
        htp.listItem(htf.anchor('demo.source_m_tx','Source of Transactional Demo ')||
                ' Need owa_util.showsource package');
        htp.listItem(htf.anchor('show_open_sessions','Opened Sessions/transactions')||
                ' Need owa_util.showsource package');
        htp.listItem(htf.anchor('../examples-jsp/forward.jsp','JSP / DBPrism interaction through jsp:forward')||
                'You can mix JSP and DBPrism mod_plsql!!!');
        htp.listItem(htf.anchor('../examples-jsp/demo.jsp','JSP / DBPrism interaction through jsp:include')||
                'You can mix JSP and DBPrism mod_plsql!!!');
        htp.listItem(htf.anchor('demo.quotes','Return a stock quotes xml message')||
                ' using xtp toolkit');
        htp.uListClose;
        link;
      htp.bodyClose;
      htp.htmlClose;
end startup;

procedure header
is
begin
        htp.htmlOpen;
        htp.headOpen;
        htp.title( 'DBPrism 2.1.1-production - mod_plsql demos' );
        htp.headClose;
        htp.bodyOpen;
end header;

procedure source
is
begin
   owa_util.showsource(cname=>'demo.startup');
end source;

procedure source_xtp
is
begin
   owa_util.showsource(cname=>'xtp.p');
end source_xtp;

procedure source_xtf
is
begin
   owa_util.showsource(cname=>'xtf.p');
end source_xtf;

procedure source_m_tx
is
begin
   owa_util.showsource(cname=>'m_tx.tx_start');
end source_m_tx;

procedure link
   is
   begin
        htp.br;
        htp.tableOpen(cattributes=>'width="100%"');
        htp.tableRowOpen;
        htp.p('<TD ALIGN=LEFT VALIGN=CENTER>');
        htp.anchor(url_server||'/xmlj/DEMOj.startup','DBPrism/Cocoon2 Java Stored Procedures Demos');
        htp.br;
        htp.p('</TD>');
        htp.p('<TD ALIGN=RIGHT VALIGN=CENTER>');
        htp.anchor('http://www.dbprism.com.ar/index.html',htf.img('../icons/onPrismSmall.gif',cattributes=>'border=0'));
        htp.p('</TD>');
        htp.tableRowClose;
        htp.tableClose;
end;

procedure jsp is
        names owa_cookie.vc_arr;
        vals owa_cookie.vc_arr;
        num_vals integer;
        i integer;
begin
        owa_util.print_cgi_env;
        htp.hr;
                 owa_cookie.get_all(names,vals,num_vals);
                 for i in 1..num_vals loop
                htp.p('Cookie =>'||names(i)||' value =>'||vals(i));
                htp.nl;
        end loop;
end jsp;

procedure forward is
begin
    header;
    jsp;
    link;
    htp.bodyClose;
    htp.htmlClose;
end forward;

procedure redirect
is
begin
    xtp.redirect_url('/index.html');
end redirect;

procedure notAuthorized
is
begin
    xtp.not_authorized('demo app');
end notAuthorized;

procedure quotes
is
begin
    xtp.header('text/xml',false);
    xtp.setHeader('xx','yy');
    xtp.send('testy',22,expires=>sysdate+2,secure=>'yes');
    xtp.closeHeader;
    xtp.prolog;
    xtp.tagOpen('PORTFOLIO');
    for x in ( select * from quote )
        loop
          xtp.tagOpen('QUOTE');
            xtp.tag('SYMBOL',x.symbol);
            xtp.tag('PRICE',x.price);
            xtp.tag('CHANGE',x.change);
          xtp.tagClose('QUOTE');
        end loop;
    xtp.tagClose('PORTFOLIO');
    xtp.epilog;
end quotes;

procedure test is
      expires_gmt date;
      expires date := sysdate-1;
   begin
      if (OWA_INIT.DBMS_SERVER_GMTDIFF is not NULL)
      then
         expires_gmt := expires-(OWA_INIT.DBMS_SERVER_GMTDIFF/24);
      else
         expires_gmt := new_time(expires,OWA_INIT.DBMS_SERVER_TIMEZONE,'GMT');
      end if;
        owa_util.mime_header('text/html',false);
        htp.print('Expires: '||
                    rtrim(to_char(expires_gmt,'Day'))||
                    to_char(expires_gmt,', DD-Mon-YY HH24:MI:SS')||' GMT');
        owa_util.http_header_close;
        htp.htmlOpen;
        htp.headOpen;
        htp.title('Sample Form');
        htp.headClose;
        htp.bodyOpen;
        htp.header(1,'Sample Form');
        htp.formOpen('demo.test');
        htp.p('Please enter a Text String:');htp.formText('b');htp.nl;
        htp.formImage('ok','../icons/ehome.gif');
        htp.formClose;
        htp.bodyClose;
        htp.htmlClose;
end;

procedure test_textarea is
      expires_gmt date;
      expires date := sysdate-1;
   begin
      if (OWA_INIT.DBMS_SERVER_GMTDIFF is not NULL)
      then
         expires_gmt := expires-(OWA_INIT.DBMS_SERVER_GMTDIFF/24);
      else
         expires_gmt := new_time(expires,OWA_INIT.DBMS_SERVER_TIMEZONE,'GMT');
      end if;
        owa_util.mime_header('text/html',false);
        htp.print('Expires: '||
                    rtrim(to_char(expires_gmt,'Day'))||
                    to_char(expires_gmt,', DD-Mon-YY HH24:MI:SS')||' GMT');
        owa_util.http_header_close;
        htp.htmlOpen;
        htp.headOpen;
        htp.title('Sample Form');
        htp.headClose;
        htp.bodyOpen;
        htp.header(1,'Sample Form');
        htp.formOpen('demo.test_textarea');
        htp.p('Please enter a huge Text String:');
        htp.formTextArea('c',12,40);
        htp.nl;
        htp.p('Please enter a huge Text String:');
        htp.formTextArea('d',12,40);
        htp.nl;
        htp.formImage('ok','../icons/ehome.gif');
        htp.formClose;
        htp.bodyClose;
        htp.htmlClose;
end;

procedure test(b varchar2,OK  owa_image.point default owa_image.null_point) is
        names owa_cookie.vc_arr;
        vals owa_cookie.vc_arr;
        num_vals integer;
        i integer;
begin
        htp.htmlOpen;
        htp.headOpen;
        htp.title('Sample Page-'||owa.auth_scheme);
        htp.headClose;
        htp.bodyOpen;
        htp.header(2,b);
        if OK is not null then
                htp.p(owa_image.get_x(OK));
                htp.p(owa_image.get_y(OK));
        end if;
        htp.hr;
        owa_util.print_cgi_env;
        htp.hr;
                 owa_cookie.get_all(names,vals,num_vals);
                 for i in 1..num_vals loop
                htp.p('Cookie =>'||names(i)||' value =>'||vals(i));
                htp.nl;
        end loop;
        htp.bodyClose;
        htp.htmlClose;
end;

procedure test_textarea(c CLOB, d CLOB, OK  owa_image.point default owa_image.null_point) is
        names owa_cookie.vc_arr;
        vals owa_cookie.vc_arr;
        num_vals integer;
        i integer;
begin
        htp.htmlOpen;
        htp.headOpen;
        htp.title('Sample Page-'||owa.auth_scheme);
        htp.headClose;
        htp.bodyOpen;
        htp.header(3,'Clob length C '||dbms_lob.getlength(c)||'  Clob isTemporary C '||dbms_lob.ISTEMPORARY(c));
        htp.header(3,'First 32K of first text area:');
        htp.hr;
        htp.code(dbms_lob.substr(c,3200));
        htp.hr;
        htp.header(3,'Clob length D '||dbms_lob.getlength(d)||'  Clob isTemporary C '||dbms_lob.ISTEMPORARY(d));
        htp.header(3,'First 32K of second text area:');
        htp.hr;
        htp.code(dbms_lob.substr(d,32000));
        htp.hr;
        if OK is not null then
                htp.p(owa_image.get_x(OK));
                htp.p(owa_image.get_y(OK));
        end if;
        htp.hr;
        owa_util.print_cgi_env;
        htp.hr;
                 owa_cookie.get_all(names,vals,num_vals);
                 for i in 1..num_vals loop
                htp.p('Cookie =>'||names(i)||' value =>'||vals(i));
                htp.nl;
        end loop;
        htp.bodyClose;
        htp.htmlClose;
end;

procedure flexible(name_array IN owa_util.ident_arr,
                   value_array owa_util.ident_arr) is
begin
        htp.htmlOpen;
        htp.headOpen;
        htp.title('Sample Page - of flexible parameters');
        htp.headClose;
        htp.bodyOpen;
        htp.hr;
        owa_util.print_cgi_env;
        htp.hr;
        if (name_array is not null and name_array.count>0) then
          for i in name_array.first..name_array.last loop
                htp.p('Name =>'||name_array(i)||' value =>'||value_array(i));
                htp.nl;
          end loop;
        end if;
        htp.bodyClose;
        htp.htmlClose;
end;

     /*********************************************************************/
    /*  PER_PACKAGE PLSQL Agent Authorization callback function -        */
   /*     It is used when PLSQL Agent's authorization scheme is set to  */
  /*      GLOBAL or CUSTOM when there is overriding OWA_CUSTOM package.*/
 /*       This is a default implementation. User should modify.       */
/*********************************************************************/
function authorize return boolean is
begin
      owa_sec.set_protection_realm('Demo Per Package Realm');
      if owa_sec.get_user_id() = 'scott' and owa_sec.get_password() = 'tiger' then
         return true;
      else
         return false;
      end if;
end;

function getfilepath return varchar2
is
        script_name             varchar2(255) default owa_util.get_cgi_env( 'SCRIPT_NAME' );
        path_info               varchar2(255) default owa_util.get_cgi_env( 'PATH_INFO' );
        pos                     number;
begin
        script_name := script_name || path_info;
        pos := instr(script_name, 'docs');
        script_name :=  substr(script_name, pos+5, length(script_name)-pos-4);
        return script_name;
end getfilepath;

procedure formopen
is
begin
    htp.p( '<form action="demo.process_upload" method=post ' ||
           'enctype="multipart/form-data">' );
end formopen;
--

function remove_invalid_chars( p_str in varchar2 ) return varchar2
is
        l_val   varchar2(255) default
                'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_./';

        l_a_good_char   char(1) default substr( l_val, 1, 1 );

        l_invalid_chars varchar2(255) default
                replace( translate( p_str, l_val, l_a_good_char ), l_a_good_char, '' );

        l_a_bad_char    char(1) default substr( l_invalid_chars, 1, 1 );
begin

        if ( l_invalid_chars is NULL ) then
                return p_str;
        end if;

        return replace(
                translate( p_str, l_invalid_chars,
                        rpad( l_a_bad_char, length(l_invalid_chars), l_a_bad_char )),
                                        l_a_bad_char, '' );
end;


procedure show_the_form
is
begin
    if ( 1=0 ) then
        htp.p( 'Sorry, Only Netscape 2.x/3.x can currently upload documents' );
    else
                htp.p( 'Keep the name "simple". Do not include blanks or special
                characters. A simple name like YourName.gif will do. Special
                characters will mess up the URL when you try to retrieve it.
                If you get a database error to the effect of "duplicate key",
                then simply use the update button instead of insert. It just
                means someone else has already used that name. Since this is a
                demo, you can just overwrite their stuff. ' );
        htp.p( '<center>' );
        formOpen;
                htp.p( 'What should the name be:' ); htp.br;
                htp.formText( 'p_name' );
                htp.br;

                htp.p( '<input type="file" name="p_doc" size="50" >' );
--
        htp.para;
        htp.formSubmit( 'p_type', cvalue => 'Insert' );
        htp.formSubmit( 'p_type', cvalue => 'Update' );
        htp.formReset;
--
        htp.formClose;
        htp.p( '</center>' );
                htp.hr;
                htp.anchor( 'demo.show_all',
                                        'Wanna peek at what others have loaded...' );
    end if;
end;


procedure show_form
is
begin
--
    header;
    show_the_form;
    link;
    htp.bodyClose;
    htp.htmlClose;
--
end show_form;
--

procedure process_upload( p_type in varchar2, p_doc in varchar2, p_name in varchar2 )
is
        l_mode varchar2(25) default 'added to ';
        l_name varchar2(255) default remove_invalid_chars( p_name );
        l_count number;
begin
    if ( owa_util.get_cgi_env('HTTP_CONTENT_TYPE') not like
                'multipart/form-data;%' )
    then
        raise_application_error( -20666,
                                'Sorry, Only Netscape 2.x/3.x can currently upload documents' );
                return;
        end if;

        select count(*) into l_count from wpg_document where name = p_doc;

        if ( l_count = 0 ) then
                raise_application_error( -20666,
                        'You uploaded a MISSING document...' );
        end if;

        header;

        if (l_name is NULL)  then
          l_name := USER || substr(p_doc, instr(p_doc, '/'));
        else
          l_name := USER || '/' || l_name;
        end if;

        if ( p_type = 'Update' ) then
                delete from wpg_document where name = l_name;
                l_mode := 'updated in';
                if ( sql%rowcount > 1 ) then
                        htp.bold( 'You have just deleted an old document' );
                        htp.br;
                end if;
        end if;
        update wpg_document set name = l_name where name = p_doc;
        commit;
        htp.p( 'Document has been ' || l_mode || ' the system as ' );
        htp.anchor( 'docs/' || l_name, htf.bold(l_name) );
        htp.hr;
        show_the_form;
        link;
        htp.bodyClose;
        htp.htmlClose;
end process_upload;

procedure show_all
is
        sn              varchar2(255) default  'docs/';
begin
        header;

        htp.tableOpen( 'border' );
        htp.tableHeader( 'name' );
        htp.tableHeader( 'mime' );
        htp.tableHeader( 'size' );
        for x in ( select name, mime_type, doc_size
                     from wpg_document where name > ' '
                    order by name )
        loop
                htp.tableRowOpen;
                htp.tableData( htf.anchor(  sn || x.name, x.name ) );
                htp.tableData( x.mime_type );
                htp.tableData( x.doc_size );
                htp.tableRowClose;
        end loop;
        htp.tableClose;
        link;
        htp.bodyClose;
        htp.htmlClose;
end show_all;


procedure process_download
is
     v_filename varchar2(255);
begin
  v_filename := getfilepath;
  select name into v_filename from wpg_document where UPPER(name) = UPPER(v_filename);
  wpg_docload.download_file(v_filename);
exception
  when others then
   v_filename := null;
end process_download;

BEGIN
   -- where can I find xml stylesheets
   url_server := owa_util.get_cgi_env('REQUEST_PROTOCOL')||'://'||
                 owa_util.get_cgi_env('SERVER_NAME')||':'||
                 owa_util.get_cgi_env('SERVER_PORT');

END demo;
/
